﻿using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Web;
using System.Data.SqlClient;



namespace MSSQLProvider.DataBase
{
    public abstract class MSSQLDataAccess
    {
        public MSSQLDataAccess()
        {
            //m_ConnectionString = ConfigurationManager.ConnectionStrings["SqlCSConnString"].ConnectionString;
            m_ConnectionString = ConfigurationSettings.AppSettings["SqlCSConnString"];
            m_commandTimeout = 0;
        }

        private string m_ConnectionString = "";
        protected string ConnectionString
        {
            get { return m_ConnectionString; }
            set { m_ConnectionString = value; }
        }

        private int m_commandTimeout;

        protected int CommandTimeout
        {
            get { return m_commandTimeout; }
            set { m_commandTimeout = value; }
        }
     
        private bool _enableCaching = true;
        protected bool EnableCaching
        {
            get { return _enableCaching; }
            set { _enableCaching = value; }
        }

        private int _cacheDuration = 0;

        protected int CacheDuration
        {
            get { return _cacheDuration; }
            set { _cacheDuration = value; }
        }

        public bool WriteLogFile
        {
            get
            {
                string s = ConfigurationSettings.AppSettings["WriteLogFile"];
                return (s.ToLower().Trim() == "true");                
            }
        }
        
        public string cast_UInt(long expr)
        {
            string str = "cast(" + expr.ToString() + " as UNSIGNED INT)";

            return str;
        }

        protected object ConvertNullObjectToDBNull(object obj)
        {
            if (obj == null)
                return DBNull.Value;
            return obj;
        }
        private void WriteErrorToLogFile(Exception ex)
        {
            if (WriteLogFile)
            {
                try
                {                   
                    //  [3/5/2012 DUCAN]
                    string logPath = HttpContext.Current.Server.MapPath("LogFiles");

                    if (!Directory.Exists(logPath))
                    {
                        Directory.CreateDirectory(logPath);
                    }

                    string logFile = "sqlLog.txt";
                    FileStream fs;
                    if (!File.Exists(logFile))
                        fs = File.Create(logFile);
                    else
                        fs = new FileStream(logFile, FileMode.Append, FileAccess.Write);

                    if (fs != null)
                    {
                        StreamWriter sw = new StreamWriter(fs);
                        if (fs.Length > 0)
                            sw.Write(Environment.NewLine);
                        sw.Write(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt") + ":\t");
                        sw.Write(ex.Message);

                        sw.Close();
                        fs.Close();
                    }

                }
                catch
                { }
            }
        }
        // using for INSERT, UPDATE, DELETE
        protected int ExecuteNonQuery(SqlCommand cmd)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    cmd.Connection = cn;
                    //cmd.CommandTimeout = TimeOut;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //2012-03-19,van
        protected int ExecuteNonQuery(string strSQL, List<string> arrPramName, List<object> arrValue)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = 60;
                    for (int i = 0; i < arrPramName.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrPramName[i], arrValue[i]));
                    }

                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected SqlDataReader ExecuteReader(SqlCommand cmd)
        {
            return ExecuteReader(cmd, CommandBehavior.Default);
        }

        protected SqlDataReader ExecuteReader(SqlCommand cmd, CommandBehavior behavior)
        {
            return cmd.ExecuteReader(behavior);
        }

        protected object ExecuteScalar(SqlCommand cmd)
        {
            return cmd.ExecuteScalar();
        }
        public DataSet ExecuteDataset(string strSQL)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = GlobalsFs.g_CommandTimeOut;
                    SqlDataAdapter adap = new SqlDataAdapter(cmd);
                    DataSet dst = new DataSet();
                    adap.Fill(dst);
                    
                    return dst;
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public DataSet Execute_Store(string strSQL, SqlParameter[] sqlParams)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.CommandTimeout = TimeOut;

                    foreach (SqlParameter param in sqlParams)
                        cmd.Parameters.Add(param);

                    SqlDataAdapter adap = new SqlDataAdapter(cmd);
                    DataSet dst = new DataSet();
                    adap.Fill(dst);
                    return dst;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // using for INSERT, UPDATE, DELETE
        protected bool ExecuteNonQuery(string strSQL)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    return cmd.ExecuteNonQuery() >= 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // using for INSERT, UPDATE, DELETE
        protected bool ExecuteNonQuery(string strSQL, SqlParameter[] sqlParams)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    foreach (SqlParameter param in sqlParams)
                        cmd.Parameters.Add(param);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        protected SqlDataReader ExecuteReader(string strSQL)
        {
            try
            {
                SqlConnection cn = new SqlConnection(m_ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                cmd.CommandType = CommandType.Text;
                //cmd.CommandTimeout = TimeOut;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                //WriteLog:
                throw ex;
                //throw;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected object ExecuteScalar(string strSQL)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    // cmd.CommandTimeout = TimeOut;
                    return cmd.ExecuteScalar();
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        public bool ExecuteProcedure(string procName, SqlParameter[] cmdParams)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(procName, cn);
                    //cmd.CommandTimeout = TimeOut;
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter myParm = cmd.Parameters.Add("?RetValue", SqlDbType.Int);
                    myParm.Direction = ParameterDirection.ReturnValue;
                    for (int i = 0; i < cmdParams.Length; i++)
                        cmd.Parameters.Add(cmdParams[i]);
                    cmd.ExecuteNonQuery();

                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //2012-03-19,van
        protected DataSet ExecuteDataset(string strSQL, List<string> arrPramName, List<object> arrValue)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    for (int i = 0; i < arrPramName.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrPramName[i], arrValue[i]));
                    }
                    
                    SqlDataAdapter adap = new SqlDataAdapter(cmd);
                    DataSet dst = new DataSet();
                    adap.Fill(dst);
                                        
                    return dst;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //2012-03-19,van
        protected object ExecuteScalar(string strSQL, List<string> arrParam, List<object> arrValue)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;

                    for (int i = 0; i < arrParam.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrParam[i], arrValue[i]));
                    }

                    object obj = cmd.ExecuteScalar();
                    
                    return obj;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }


        //2012-03-19,van
        protected Hashtable ExecuteHashtable(string query)
        {
            SqlDataReader reader = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    cmd.CommandType = CommandType.Text;

                    //cmd.CommandTimeout = TimeOut;

                    reader = cmd.ExecuteReader();
                    Hashtable hsObjs = null;
                    if (reader.Read())
                    {
                        hsObjs = new Hashtable();
                        string fieldName;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            fieldName = reader.GetName(i).ToLower();
                            if (!hsObjs.ContainsKey(fieldName))
                                hsObjs.Add(fieldName, reader[i]);
                        }
                    }
                    
                    return hsObjs;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
        }

        //2012-03-19
        protected Hashtable ExecuteHashtable(string query, List<string> arrParam, List<object> arrValue)
        {
            SqlDataReader reader = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    for (int i = 0; i < arrParam.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrParam[i], arrValue[i]));
                    }
                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    Hashtable hsObjs = null;
                    if (reader.Read())
                    {
                        hsObjs = new Hashtable();
                        string fieldName;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            fieldName = reader.GetName(i).ToLower();
                            if (!hsObjs.ContainsKey(fieldName))
                                hsObjs.Add(fieldName, reader[i]);
                        }
                    }

                    return hsObjs;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

        }

        //2012-03-19,van
        protected List<object> GetListObject(string strSQL)
        {
            List<object> arrObj = new List<object>();

            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    reader = cmd.ExecuteReader();

                    object obj;

                    while (reader.Read())
                    {
                        obj = reader.GetValue(0);
                        arrObj.Add(obj);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            return arrObj;
        }//end

        //2012-03-19,van
        protected List<object> GetListObject(string strSQL, List<string> arrPram, List<object> arrValue)
        {
            List<object> arrObj = new List<object>();

            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    for (int i = 0; i < arrPram.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrPram[i], arrValue[i]));
                    }
                    reader = cmd.ExecuteReader();

                    object obj;

                    while (reader.Read())
                    {
                        obj = reader.GetValue(0);
                        arrObj.Add(obj);
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            return arrObj;
        }//end

        /// <summary>
        /// get array of item that item is array of object
        /// </summary>
        /// 2012-03-19,van; 
        /// <param name="strSQL"></param>
        /// <returns></returns>
        protected ArrayList GetArrayObjects(string strSQL)
        {
            ArrayList arrObjs = new ArrayList();

            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    //cmd.CommandTimeout = TimeOut;
                    reader = cmd.ExecuteReader();

                    object[] objs;

                    while (reader.Read())
                    {
                        objs = new object[reader.FieldCount];
                        reader.GetValues(objs);
                        arrObjs.Add(objs);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            return arrObjs;
        }

        //2012-03-19,van
        public static string ArrayStringToStringFilter(List<string> lstString)
        {
            if (lstString == null || lstString.Count == 0)
                return "''";
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < lstString.Count; i++)
            {
                strBuilder.Append("'" + lstString[i] + "'");
                if (i < lstString.Count - 1)
                    strBuilder.Append(",");
            }
            return strBuilder.ToString();
        }

        //2012-03-19,van
        public static string ArrayStringToStringFilter(string[] arrItem)
        {
            if (arrItem == null || arrItem.Length == 0)
                return "''";
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < arrItem.Length; i++)
            {
                strBuilder.Append("'" + arrItem[i] + "'");
                if (i < arrItem.Length - 1)
                    strBuilder.Append(",");
            }
            return strBuilder.ToString();
        }

        //2012-03-19,van
        public static string ArrayIntToStringFilter(List<int> lstString)
        {
            if (lstString == null || lstString.Count == 0)
                return "''";
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < lstString.Count; i++)
            {
                strBuilder.Append(lstString[i].ToString());
                if (i < lstString.Count - 1)
                    strBuilder.Append(",");
            }
            return strBuilder.ToString();
        }



        public static string ArrayInt64ToStringFilter(List<long> lstValue)
        {
            if (lstValue == null || lstValue.Count == 0)
                return "''";
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < lstValue.Count; i++)
            {
                strBuilder.Append(lstValue[i].ToString());
                if (i < lstValue.Count - 1)
                    strBuilder.Append(",");
            }
            return strBuilder.ToString();
        }

        //2012-03-20,van
        protected bool ExecuteNonQuery(string strSQL, List<string> arrPram, List<object> arrValue, SqlParameter[] sqlParams)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;
                    for (int i = 0; i < arrPram.Count; i++)
                    {
                        cmd.Parameters.Add(new SqlParameter(arrPram[i], arrValue[i]));
                    }
                    foreach (SqlParameter param in sqlParams)
                        cmd.Parameters.Add(param);
                    return cmd.ExecuteNonQuery() > 0;
                }
            }
            catch (Exception ex)
            {
                WriteErrorToLogFile(ex);
                return false;
            }
        }

        // [26-03-2012] @Thi 
        public int ExecuteNonQueryReturnInt(string strSQL)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;

                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                WriteErrorToLogFile(ex);
                return 0;
            }
        }

        //2012-03-30,van
        protected List<Hashtable> GetArrayHashtable(string query)
        {
            SqlDataReader reader = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    cmd.CommandType = CommandType.Text;

                    //cmd.CommandTimeout = TimeOut;

                    reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    Hashtable hsObjs = null;
                    List<Hashtable> arrHsObj = new List<Hashtable>();
                    string fieldName;
                    int i;
                    while (reader.Read())
                    {
                        hsObjs = new Hashtable();
                        for (i = 0; i < reader.FieldCount; i++)
                        {
                            fieldName = reader.GetName(i).ToLower();
                            if (!hsObjs.ContainsKey(fieldName))
                                hsObjs.Add(fieldName, reader[i]);
                        }
                        arrHsObj.Add(hsObjs);
                    }

                    return arrHsObj;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if(reader != null)
                    reader.Close();
            }

        }

        //2012-08-14,van
        protected SqlDataReader ExecuteReader(string strSQL, List<string> arrParam, List<object> arrValue)
        {
            try
            {
                SqlConnection cn = new SqlConnection(m_ConnectionString);
                cn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, cn);
                for (int i = 0; i < arrParam.Count; i++)
                {
                    cmd.Parameters.Add(new SqlParameter(arrParam[i], arrValue[i]));
                }
                cmd.CommandType = CommandType.Text;
                //cmd.CommandTimeout = TimeOut;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                //WriteLog:
                throw ex;
                //throw;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //2012-03-21,van
        protected List<Hashtable> ExecuteArrayHastable(string query)
        {
            SqlDataReader reader = null;
            try
            {
                List<Hashtable> arrHasObj = new List<Hashtable>();

                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    cmd.CommandType = CommandType.Text;

                    if (m_commandTimeout > 0)
                        cmd.CommandTimeout = m_commandTimeout;

                    reader = cmd.ExecuteReader();
                    Hashtable hsObjs = null;
                    while (reader.Read())
                    {
                        hsObjs = new Hashtable();
                        string fieldName;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            fieldName = reader.GetName(i).ToLower();
                            if (!hsObjs.ContainsKey(fieldName))
                                hsObjs.Add(fieldName, reader[i]);
                        }
                        arrHasObj.Add(hsObjs);
                    }

                    return arrHasObj;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        //2012-04-11,van
        protected object[] GetObjects(string strSQL)
        {
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                SqlDataReader reader = null;
                try
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cn);
                    cmd.CommandType = CommandType.Text;

                    if (m_commandTimeout > 0)
                        cmd.CommandTimeout = m_commandTimeout;

                    reader = cmd.ExecuteReader();

                    object[] objs = null;

                    if (reader.Read())
                    {
                        objs = new object[reader.FieldCount];
                        reader.GetValues(objs);
                    }

                    return objs;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }

        protected object[] GetObjects(string strSQL, List<string> arrayParamName, List<object> arrayValue)
        {
            using (SqlConnection cn = new SqlConnection(m_ConnectionString))
            {
                SqlConnection sqlConn = null;
                SqlCommand sqlCmd = null;
                SqlDataReader reader = null;

                try
                {
                    sqlConn = new SqlConnection(m_ConnectionString);
                    sqlConn.Open();

                    sqlCmd = new SqlCommand(strSQL, cn);

                    sqlCmd.CommandText = strSQL;
                    sqlCmd.Connection = sqlConn;

                    if (m_commandTimeout > 0)
                        sqlCmd.CommandTimeout = m_commandTimeout;

                    if (arrayParamName != null && arrayValue != null && arrayParamName.Count == arrayValue.Count)
                    {
                        for (int i = 0; i < arrayParamName.Count; i++)
                        {
                            sqlCmd.Parameters.Add(new SqlParameter(arrayParamName[i], arrayValue[i]));
                        }
                    }

                    reader = sqlCmd.ExecuteReader();

                    object[] objs = null;

                    if (reader.Read())
                    {
                        objs = new object[reader.FieldCount];
                        reader.GetValues(objs);
                    }

                    return objs;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
        }

        //2012-04-18,van
        protected List<object> GetListObject2(string strSQL, List<string> arrayParamName, List<object> arrayValue)
        {
             SqlCommand sqlCmd = null;
            SqlDataReader reader = null;
            List<object> arrObject = new List<object>();
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(m_ConnectionString))
                {
                    sqlConn.Open();

                    sqlCmd = new SqlCommand();

                    sqlCmd.CommandText = strSQL;
                    sqlCmd.Connection = sqlConn;

                    if (m_commandTimeout > 0)
                        sqlCmd.CommandTimeout = m_commandTimeout;

                    if (arrayParamName != null && arrayValue != null && arrayParamName.Count == arrayValue.Count)
                    {
                        for (int i = 0; i < arrayParamName.Count; i++)
                        {
                            sqlCmd.Parameters.Add(new SqlParameter(arrayParamName[i], arrayValue[i]));
                        }
                    }

                    reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
                    object obj;
                    while (reader.Read())
                    {
                        obj = reader.GetValue(0);
                        arrObject.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return arrObject;
        }//end

        protected List<Hashtable> ExecuteArrayHastable(string query, List<string> arrayParamName, List<object> arrayValue)
        {
            SqlDataReader reader = null;
            try
            {
                List<Hashtable> arrHasObj = new List<Hashtable>();

                using (SqlConnection cn = new SqlConnection(m_ConnectionString))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    cmd.CommandType = CommandType.Text;

                    if (m_commandTimeout > 0)
                        cmd.CommandTimeout = m_commandTimeout;
                    if (arrayParamName != null && arrayValue != null && arrayParamName.Count == arrayValue.Count)
                    {
                        for (int i = 0; i < arrayParamName.Count; i++)
                        {
                            cmd.Parameters.Add(new SqlParameter(arrayParamName[i], arrayValue[i]));
                        }
                    }

                    reader = cmd.ExecuteReader();
                    Hashtable hsObjs = null;
                    while (reader.Read())
                    {
                        hsObjs = new Hashtable();
                        string fieldName;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            fieldName = reader.GetName(i).ToLower();
                            if (!hsObjs.ContainsKey(fieldName))
                                hsObjs.Add(fieldName, reader[i]);
                        }
                        arrHasObj.Add(hsObjs);
                    }

                    return arrHasObj;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }
    }

}
